const { getPool } = require('../config/db')
class Menu {
  static async create(menu_name, menu_title, route_path, vue_file_path, parent_menu_id) {
    const mysqlPool = await getPool()
    const [rows] = await mysqlPool.execute('INSERT INTO menus (menu_name, menu_title, route_path, vue_file_path, parent_menu_id) VALUES (?,?,?,?,?)', [menu_name, menu_title, route_path, vue_file_path, parent_menu_id])
    return rows.insertId
  }

  static async update(menu_name, menu_title, route_path, vue_file_path, parent_menu_id, menu_id) {
    const mysqlPool = await getPool()
    const [rows] = await mysqlPool.execute('UPDATE menus SET menu_name =?, menu_title =?, route_path =?, vue_file_path =?,parent_menu_id =? WHERE menu_id =?', [menu_name, menu_title, route_path, vue_file_path, parent_menu_id, menu_id])

    return rows
  }

  static async deleteById(menuId) {
    const mysqlPool = await getPool()
    const [rows] = await mysqlPool.execute('DELETE FROM menus WHERE menu_id =?', [menuId])

    return rows
  }

  static async findById(menuId) {
    const mysqlPool = await getPool()
    const [rows] = await mysqlPool.execute('SELECT * FROM menus WHERE menu_id =?', [menuId])

    return rows[0]
  }

  static async findAll() {
    // 获取数据库连接池
    const mysqlPool = await getPool()
    // 执行查询语句，从menus表中获取所有记录
    const [rows] = await mysqlPool.execute('SELECT * FROM menus ORDER BY sort_order ASC')

    // 返回查询结果
    return rows
  }
  static async findByRoleId(role_id) {
    const mysqlPool = await getPool()
    const SQL = `
    WITH RECURSIVE MenuTree AS (
      SELECT m.*
      FROM menus m
      JOIN role_menu_relations rmr ON m.menu_id = rmr.menu_id
      WHERE rmr.role_id = ?

      UNION ALL

      SELECT m.*
      FROM menus m
      JOIN MenuTree mt ON m.menu_id = mt.parent_menu_id
    )
    SELECT DISTINCT * FROM MenuTree;`
    const [rows] = await mysqlPool.execute(SQL, [role_id])

    const buildTree = (menus, parentId = null) => {
      const t1 = menus.filter((menu) => menu.parent_menu_id === parentId)
      const t2 = t1.map((menu) => ({
        ...menu,
        children: buildTree(menus, menu.menu_id),
      }))
      return t2
    }
    const res = buildTree(rows)
    return res
  }
  static async #buildMenuTree(menus, parentId = null) {
    const tree = []
    for (const menu of menus) {
      if (menu.parent_menu_id === parentId) {
        const children = await this.#buildMenuTree(menus, menu.menu_id)
        if (children.length > 0) {
          menu.children = children
        }
        tree.push(menu)
      }
    }
    return tree
  }

  static async getMenuTree() {
    const menus = await this.findAll()
    const menuTree = await this.#buildMenuTree(menus)
    return menuTree
  }
}

module.exports = Menu
